The present invention relates generally to information processing environments and, more particularly, to processing of queries against information stored in a data processing system, such as an SQL Relational Database Management System (RDBMS).
Computers are very powerful tools for storing and providing access to vast amounts of information. Computer databases are a common mechanism for storing information on computer systems while providing easy access to users. A typical database is an organized collection of related information stored as "records" having "fields" of information. As an example, a database of employees may have a record for each employee. Here, each record contains fields designating specifics about the employee, such as name, home address, salary, and the like.
Between the actual physical database itself (i.e., the records contained in data pages stored on a storage device) and the users of the system, a database management system or DBMS is typically provided as a software cushion or layer. In essence, the DBMS shields the database user from knowing or even caring about underlying hardware-level details. Typically, all requests from users for access to the data are processed by the DBMS. For example, information may be added or removed from data files, information retrieved from or updated in such files, and so forth, all without user knowledge of underlying system implementation. In this manner, the DBMS provides users with a conceptual view of the database that is removed from the hardware level. The general construction and operation of a database management system is known in the art. See e.g., Date, C., An Introduction to Database Systems, Volume I and II, Addison Wesley, 1990; the disclosure of which is hereby incorporated by reference.
DBMS systems have long since moved from a centralized mainframe environment to a de-centralized or distributed environment. One or more PC "client" systems, for instance, may be connected via a network to one or more server-based database systems (SQL database server). Commercial examples of these "client/server" systems include Powersoft.TM. clients connected to one or more Sybase SQL Server.TM. database servers. Both Powersoft.TM. and Sybase SQL Server.TM. are available from Sybase, Inc. of Emeryville, Calif. As the migration to client/server continues, each day more and more businesses are run from mission-critical systems which store information on server-based SQL database systems, such as Sybase SQL Server.TM.. As a result, increasingly higher demands are being placed on server-based SQL database systems to provide enterprise-wide decision support--providing timely on-line access to critical business information (e.g., through "queries"). Accordingly, there is much interest in improving the performance of such systems, particularly in the area of database query performance.
Traditionally, database management systems (e.g., the above-described client/server database systems) have been employed for on-line transaction processing (OLTP)--posting data (from transactions) to a database table. As part of this process of putting data "in" a database, OLTP systems typically process queries which find a single record, or just a few records. A typical query in an OLTP system, for instance, might be to retrieve data records for a particular customer, such as retrieving records in an airline reservation system for Account No. 35. Thus, use of OLTP systems for retrieving data has been largely limited to moment-to-moment operational needs, where the queries are relatively simple and the number of rows retrieved (relative to table size) is few. In other words, OLTP systems have been optimized for this task of finding a "needle in a haystack"--that is, finding one or few records which meet a given query condition.
More recently, however, there has been great interest in "data warehousing." For these Decision Support Systems (DSS) applications, database systems are designed not so much for putting information "in" (i.e., transaction processing) but more for getting information "out." The general approach to providing DSS has been to take an SQL-based, OLTP database engine (e.g., Sybase or Oracle) which was really architected for the OLTP environment (and the model that it represents) and attempt to extend that engine to handle DSS applications. As a result of the effort to build DSS functionality on top of OLTP database engines, the performance of present-day DSS products is generally poor. Quite simply, OLTP database engines have been architected and optimized for transaction processing to such an extent that they are not well-suited for analytical processing.
Alternatively, some vendors have implemented non-SQL-based systems providing DSS capability. These systems exist today, in large part, due to the failure of SQL-based database systems at processing DSS tasks. Examples of these non-SQL or "on-line analytical processing" (OLAP) systems include Pilot.TM. and Comshare.TM.. These systems employ a non-relational model, one which generally allows the user to view the data in a cube-like form. Typically, data records are imported from SQL databases and then stored in various proprietary formats. Once transposed from SQL format into a particular proprietary format, the information is usually maintained on one or more servers for access by clients, through a proprietary interface. These systems typically employ proprietary clients or front ends as well.
This approach also entails significant disadvantages, however. Users prefer to not have to import and/or transpose information into various proprietary formats. Instead, user would rather simply keep the information in SQL database tables. There are at least two reasons for this. First, the information is generally "already there," having been accumulated by the posting of transactions over a period of time. Second, users are already familiar with working with one of the main SQL database providers, such as Sybase or Oracle. Even if a separate tool is required to implement DSS, users prefer to work with tools which are in the same family as the tools which they are already using on a day-to-day basis. Expectedly, the non-SQL approach has met with limited success.
What is needed are system and methods with better DSS performance, yet within the traditional SQL/relational model--a model which users demand. From the perspective of users, such a system should appear to be essentially an SQL-based relational system. At the same time, however, such a system should yield much-improved DSS performance. The present invention fulfills this and other needs.